create database AppleShop
go
use AppleShop
go
create table Admins
(
AdminId varchar(10) constraint PK_AdminId primary key,
AdminPass varchar(30) not null
)
go
create table UserAccount
(
UserName varchar(30) constraint PK_UserName Primary Key,
UserPass varchar(30)not null,
UserFullName varchar(50) not null,
UserSex varchar(10) not null,
UserBirthday varchar(50) not null,
UserAddress varchar(50)not null,
UserPhone Varchar(20)not null,
UserEmail varchar(50) not null,
UserRegisDate Date Constraint DF_NgayDangKy Default(GetDate())
)
go
create table ProductType
(
TypeID varchar(50) constraint PK_TypeID Primary key,
TypeName varchar(50) not null
)
go
create table Product
(
ProductsID varchar(50) constraint PK_ProductsID Primary key,
ProductsName varchar(50) not null,
ProductsTypeName varchar(50) not null constraint FK_ProductsTypeName foreign key References ProductType(TypeID) on delete cascade,
ProductsPrice float not null,
ProductsImage varchar(200) not null,
ProductsIsDel int not null default 1
)
go
create table ProductDetails
(
DetailsNo int identity(1,1) constraint PK_DetailsNo Primary key,
DetailsID varchar(50) constraint FK_DetailsID foreign key References Product(ProductsID) on delete cascade not null,
DetailsImage varchar(200) not null,
DetailsText varchar(500)
)
go
-- insert admin table
insert into Admins values('admin','admin')
go
-- insert admin table
insert into UserAccount(UserName, UserPass, UserFullName, UserSex, UserBirthday, UserAddress, UserPhone, UserEmail) 
			values('vuserip','lanhuthe','Huynh Dao Hoang Vu','Male','19/12/1992','95 tran hung dao','01287698263','huynhdaohoangvu_py@yahoo.com')
go			
insert into UserAccount(UserName, UserPass, UserFullName, UserSex, UserBirthday, UserAddress, UserPhone, UserEmail) 
			values('nhokbean','minhtri','Nguyen Minh Tri','Male','04/03/1992','52 Dinh Tien Hoang','0938764183','nhokbean@yahoo.com')
go
insert into UserAccount(UserName, UserPass, UserFullName, UserSex, UserBirthday, UserAddress, UserPhone, UserEmail) 
			values('thanhtuanfh','123123','Ngo Thanh Tuan','Male','24/03/1991','Tang 68 Bitexco','01224750150','thanhtuanfh@yahoo.com')
go
insert into UserAccount(UserName, UserPass, UserFullName, UserSex, UserBirthday, UserAddress, UserPhone, UserEmail) 
			values('eyesonme','dungvaynhe','Ngo Tri Dung','Male','30/08/1988','Nga 5 Chuong Cho','0933283567','eyesonme2550@yahoo.com')
go
insert into UserAccount(UserName, UserPass, UserFullName, UserSex, UserBirthday, UserAddress, UserPhone, UserEmail) 
			values('akateiru','trungchi','Le Duy Trieu','Male','04/08/1992','Gan Truong Tran Phu','0903034892','aka_teiru2010@yahoo.com')		

go
--insert products Type
insert into ProductType values('iPad','iPad')
insert into ProductType values('iPhone','iPhone')
insert into ProductType values('iPod','iPod')
insert into ProductType values('Macbook Air','Macbook Air')
insert into ProductType values('Macbook Pro','Macbook Pro')
insert into ProductType values('Mac Mini','Mac Mini')
insert into ProductType values('iMac','iMac')
insert into ProductType values('Mac Pro','Mac Pro')
insert into ProductType values('Apple TV','Apple TV')
go
--insert products
insert into Product(ProductsID, ProductsName, ProductsTypeName, ProductsPrice, ProductsImage) 
			values('Iphone4S 16GB World','Iphone4S 16GB World','iPhone',549,'~/IMG/iPhone/Iphone4S-World.jpg')
insert into Product(ProductsID, ProductsName, ProductsTypeName, ProductsPrice, ProductsImage) 
			values('Iphone4S 32GB World','Iphone4S 32GB World','iPhone',649,'~/IMG/iPhone/Iphone4S-World.jpg')
insert into Product(ProductsID, ProductsName, ProductsTypeName, ProductsPrice, ProductsImage) 
			values('Iphone4S 64GB World','Iphone4S 64GB World','iPhone',749,'~/IMG/iPhone/Iphone4S-World.jpg')
insert into Product(ProductsID, ProductsName, ProductsTypeName, ProductsPrice, ProductsImage) 
			values('Iphone5 16GB World','Iphone5 16GB World','iPhone',649,'~/IMG/iPhone/Iphone5-World.jpg')
insert into Product(ProductsID, ProductsName, ProductsTypeName, ProductsPrice, ProductsImage) 
			values('Iphone5 32GB World','Iphone5 32GB World','iPhone',749,'~/IMG/iPhone/Iphone5-World.jpg')
insert into Product(ProductsID, ProductsName, ProductsTypeName, ProductsPrice, ProductsImage) 
			values('Iphone5 64GB World','Iphone5 64GB World','iPhone',849,'~/IMG/iPhone/Iphone5-World.jpg')

go
--insert products Details
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone4S 16GB World','~/IMG/iPhone/Iphone4S-16GB-World-Details1.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone4S 16GB World','~/IMG/iPhone/Iphone4S-16GB-World-Details2.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone4S 16GB World','~/IMG/iPhone/Iphone4S-16GB-World-Details3.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone4S 16GB World','~/IMG/iPhone/Iphone4S-16GB-World-Details4.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone4S 16GB World','~/IMG/iPhone/Iphone4S-16GB-World-Details5.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone4S 16GB World','~/IMG/iPhone/Iphone4S-16GB-World-Details6.jpg','...')

insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone5 32GB World','~/IMG/iPhone/Iphone5-32GB-World-Details1.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone5 32GB World','~/IMG/iPhone/Iphone5-32GB-World-Details2.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone5 32GB World','~/IMG/iPhone/Iphone5-32GB-World-Details3.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone5 32GB World','~/IMG/iPhone/Iphone5-32GB-World-Details4.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone5 32GB World','~/IMG/iPhone/Iphone5-32GB-World-Details5.jpg','...')
insert into ProductDetails(DetailsID, DetailsImage, DetailsText) values('Iphone5 32GB World','~/IMG/iPhone/Iphone5-32GB-World-Details6.jpg','...')		

-- procedure select User to Check Login
go
create procedure spGetUser
@username varchar(30)	
as
	begin
		select * from UserAccount where UserName = @username
	end		
-- procedure Register New User
go
create procedure spAddnewUser
@UserName varchar(30),
@UserPass varchar(30),
@UserFullName varchar(50),
@UserSex varchar(10),
@UserBirthday varchar(50),
@UserAddress varchar(50),
@UserPhone Varchar(20),
@UserEmail varchar(50)
as
	begin
		Insert into UserAccount(UserName, UserPass, UserFullName, UserSex, UserBirthday, UserAddress, UserPhone, UserEmail) 
		values(@UserName, @UserPass, @UserFullName, @UserSex, @UserBirthday, @UserAddress, @UserPhone, @UserEmail)
	end				